unit uCorrigeCidade;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Formulario_Padrao_Rel, DBTables, Grids, DBGrids, DB, DBClient,
  RDprint, ExtCtrls, StdCtrls, CheckLst, ComCtrls, TFlatButtonUnit, Buttons,
  ImgList, BmsXPProgressBar;

type
  TfrmCorrigeCidades = class(TFrmPad_Rel)
    CDS: TClientDataSet;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    CDSCidade: TStringField;
    CDSCod1: TStringField;
    CDSCod3: TStringField;
    ImageList1: TImageList;
    CDSCod2: TStringField;
    CDSOK1: TBooleanField;
    CDSOK2: TBooleanField;
    CDSOK3: TBooleanField;
    pb: TBmsXPProgressBar;
    CDSUF1: TStringField;
    CDSUF2: TStringField;
    CDSUF3: TStringField;
    procedure FormActivate(Sender: TObject);
    procedure DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TColumn; State: TGridDrawState);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure DBGrid1ColEnter(Sender: TObject);
    procedure btncancelarClick(Sender: TObject);
    procedure bimprimirClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmCorrigeCidades: TfrmCorrigeCidades;
  tq: TQuery;

implementation

uses Dm;

{$R *.dfm}

procedure TfrmCorrigeCidades.FormActivate(Sender: TObject);
begin
  inherited;
  CDS.Close;
  CDS.CreateDataSet;
  CDS.Open;
  tq := TQuery.Create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.Close;
  tq.SQL.Clear;
  tq.Params.Clear;
  tq.SQL.Add('select cid_codigo, cid_nome, est_sigla ' +
    'from cidade ' +
    'where cid_nome in ' +
    '( ' +
    'select cid_nome ' +
    'from cidade ' +
    'group by cid_nome, est_sigla ' +
    'having count(*) > 1 ' +
    ') ' +
    'and cid_codigo <> ' + QuotedStr('0944') +
    'order by cid_nome, cid_codigo');
  tq.Open;
  while not tq.Eof do begin
    if not CDS.Locate('Cidade', Trim(tq.FieldByName('Cid_Nome').AsString), []) then begin
      CDS.Append;
      CDSCidade.Value := Trim(tq.FieldByName('Cid_Nome').AsString);
      CDSCod1.Value := tq.FieldByName('cid_codigo').AsString + '  ' + tq.FieldByName('est_sigla').AsString;
      CDSUF1.Value := tq.FieldByName('est_sigla').AsString;
      CDSOK1.Value := True;
    end else begin
      CDS.Edit;
      if (CDSCod1.Value <> '') and (CDSCod2.Value <> '') then
        CDSCod3.Value := tq.FieldByName('cid_codigo').AsString + '  ' + tq.FieldByName('est_sigla').AsString;
      if (CDSCod1.Value <> '') and (CDSCod2.Value = '') then
        CDSCod2.Value := tq.FieldByName('cid_codigo').AsString + '  ' + tq.FieldByName('est_sigla').AsString;
    end;
    CDS.Post;
    tq.Next;
  end;
  cds.first;
  tq.Close;
  tq.Free;
end;

procedure TfrmCorrigeCidades.DBGrid1DrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn;
  State: TGridDrawState);
begin
  inherited;
  if (Column.Field = CDSOK1) then begin
    DBGrid1.Canvas.FillRect(Rect);
    ImageList1.Draw(DBGrid1.Canvas, Rect.Left + 10, Rect.Top + 1, 0);
    if CDSOK1.AsBoolean then begin
      ImageList1.Draw(DBGrid1.Canvas, Rect.Left + 10, Rect.Top + 1, 2);
    end else begin
      ImageList1.Draw(DBGrid1.Canvas, Rect.Left + 10, Rect.Top + 1, 1);
    end;
  end;

  if (Column.Field = CDSOK2) then begin
    DBGrid1.Canvas.FillRect(Rect);
    ImageList1.Draw(DBGrid1.Canvas, Rect.Left + 10, Rect.Top + 1, 0);
    if CDSOK2.AsBoolean then begin
      ImageList1.Draw(DBGrid1.Canvas, Rect.Left + 10, Rect.Top + 1, 2);
    end else begin
      ImageList1.Draw(DBGrid1.Canvas, Rect.Left + 10, Rect.Top + 1, 1);
    end;
  end;

  if (Column.Field = CDSOK3) then begin
    DBGrid1.Canvas.FillRect(Rect);
    ImageList1.Draw(DBGrid1.Canvas, Rect.Left + 10, Rect.Top + 1, 0);
    if CDSOK3.AsBoolean then begin
      ImageList1.Draw(DBGrid1.Canvas, Rect.Left + 10, Rect.Top + 1, 2);
    end else begin
      ImageList1.Draw(DBGrid1.Canvas, Rect.Left + 10, Rect.Top + 1, 1);
    end;
  end;

end;

procedure TfrmCorrigeCidades.DBGrid1CellClick(Column: TColumn);
begin
  inherited;
  if Column.Field = CDSOK1 then begin
    CDS.Edit;
    CDSOK1.Value := not CDSOK1.Value;
    if CDSOK1.AsBoolean then begin
      CDSOK2.Value := False;
      CDSOK3.Value := False;
    end;
    CDS.Post;
  end;

  if Column.Field = CDSOK2 then begin
    CDS.Edit;
    CDSOK2.Value := not CDSOK2.Value;
    if CDSOK2.AsBoolean then begin
      CDSOK1.Value := False;
      CDSOK3.Value := False;
    end;
    CDS.Post;
  end;

  if Column.Field = CDSOK3 then begin
    CDS.Edit;
    CDSOK3.Value := not CDSOK3.Value;
    if CDSOK3.AsBoolean then begin
      CDSOK1.Value := False;
      CDSOK2.Value := False;
    end;
    CDS.Post;
  end;

end;

procedure TfrmCorrigeCidades.DBGrid1ColEnter(Sender: TObject);
begin
  inherited;
  if (DBGrid1.SelectedField = CDSOK1) or (DBGrid1.SelectedField = CDSOK2) or (DBGrid1.SelectedField = CDSOK3) then
    DBGrid1.Options := DBGrid1.Options - [dgEditing]
  else
    DBGrid1.Options := DBGrid1.Options + [dgEditing];
end;

procedure TfrmCorrigeCidades.btncancelarClick(Sender: TObject);
begin
  inherited;
  FormActivate(Sender);
end;

procedure TfrmCorrigeCidades.bimprimirClick(Sender: TObject);
var
  tq: TQuery;
  CA,
    CB,
    CC: string;
  corrige: Boolean;
begin
  inherited;
  CDS.First;
  pb.Min := 0;
  pb.Max := CDS.RecordCount;
  pb.Position := 0;

  tq := TQuery.Create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;

  while not CDS.Eof do begin
    pb.Position := pb.Position + 1;

    if CDSOK1.AsBoolean then begin
      CA := copy(CDSCod1.Value, 1, 4);
      CB := copy(CDSCod2.Value, 1, 4);
      CC := copy(CDSCod3.Value, 1, 4);
    end;
    if CDSOK2.AsBoolean then begin
      CA := copy(CDSCod2.Value, 1, 4);
      CB := copy(CDSCod1.Value, 1, 4);
      CC := copy(CDSCod3.Value, 1, 4);
    end;
    if CDSOK3.AsBoolean then begin
      CA := copy(CDSCod3.Value, 1, 4);
      CB := copy(CDSCod1.Value, 1, 4);
      CC := copy(CDSCod2.Value, 1, 4);
    end;

    DmInt.db.StartTransaction;
    try
      tq.Close;
      tq.SQL.Clear;
      tq.Params.Clear;
      tq.SQL.Add('Update EMPRESA set Cid_Codigo =:CidPrincipal ' +
        'where ((Cid_Codigo =:CidDupli1) or (Cid_Codigo =:CidDupli2)) and Cid_Codigo is not null');
      tq.ParamByName('CidPrincipal').AsString := CA;
      tq.ParamByName('CidDupli1').AsString := CB;
      tq.ParamByName('CidDupli2').AsString := CC;
      tq.ExecSQL;

      tq.Close;
      tq.SQL.Clear;
      tq.Params.Clear;
      tq.SQL.Add('Update ENDERECO set Cid_Codigo =:CidPrincipal ' +
        'where ((Cid_Codigo =:CidDupli1) or (Cid_Codigo =:CidDupli2)) and Cid_Codigo is not null');
      tq.ParamByName('CidPrincipal').AsString := CA;
      tq.ParamByName('CidDupli1').AsString := CB;
      tq.ParamByName('CidDupli2').AsString := CC;
      tq.ExecSQL;

      tq.Close;
      tq.SQL.Clear;
      tq.Params.Clear;
      tq.SQL.Add('Update LOCALCOBRANCA set Cid_Codigo =:CidPrincipal ' +
        'where ((Cid_Codigo =:CidDupli1) or (Cid_Codigo =:CidDupli2)) and Cid_Codigo is not null');
      tq.ParamByName('CidPrincipal').AsString := CA;
      tq.ParamByName('CidDupli1').AsString := CB;
      tq.ParamByName('CidDupli2').AsString := CC;
      tq.ExecSQL;

      tq.Close;
      tq.SQL.Clear;
      tq.Params.Clear;
      tq.SQL.Add('Update REPRESENTANTE set Cid_Codigo =:CidPrincipal ' +
        'where ((Cid_Codigo =:CidDupli1) or (Cid_Codigo =:CidDupli2)) and Cid_Codigo is not null');
      tq.ParamByName('CidPrincipal').AsString := CA;
      tq.ParamByName('CidDupli1').AsString := CB;
      tq.ParamByName('CidDupli2').AsString := CC;
      tq.ExecSQL;

      tq.Close;
      tq.SQL.Clear;
      tq.Params.Clear;
      tq.SQL.Add('Update SUPERVISOR set Cid_Codigo =:CidPrincipal ' +
        'where ((Cid_Codigo =:CidDupli1) or (Cid_Codigo =:CidDupli2)) and Cid_Codigo is not null');
      tq.ParamByName('CidPrincipal').AsString := CA;
      tq.ParamByName('CidDupli1').AsString := CB;
      tq.ParamByName('CidDupli2').AsString := CC;
      tq.ExecSQL;

      tq.Close;
      tq.SQL.Clear;
      tq.Params.Clear;
      tq.SQL.Add('Update TRANSPORTADORA set Cid_Codigo =:CidPrincipal ' +
        'where ((Cid_Codigo =:CidDupli1) or (Cid_Codigo =:CidDupli2)) and Cid_Codigo is not null');
      tq.ParamByName('CidPrincipal').AsString := CA;
      tq.ParamByName('CidDupli1').AsString := CB;
      tq.ParamByName('CidDupli2').AsString := CC;
      tq.ExecSQL;

      tq.Close;
      tq.SQL.Clear;
      tq.Params.Clear;
      tq.SQL.Add('Delete from Cidade ' +
        'where ((Cid_Codigo =:CidDupli1) or (Cid_Codigo =:CidDupli2)) and Cid_Codigo is not null');
      tq.ParamByName('CidDupli1').AsString := CB;
      tq.ParamByName('CidDupli2').AsString := CC;
      tq.ExecSQL;

      DmInt.Db.Commit;
    except
      ShowMessage('Erro ao atualizar cidades');
      DmInt.Db.Rollback;
    end;

    CDS.Next;
  end;

  FormActivate(sender);
  tq.Close;
  tq.Free;
end;

end.
